A database management system (DBMS) facilitates interaction with database data. The DBMS efficiently manages requests or queries from users and programs so that these entities are free from having to know specifics pertaining to how and where data is physically stored. Furthermore, in handling requests, the DBMS ensures data integrity and security. Although other systems are emerging, the relational database management system (RDBMS) remains the most common DBMS.
A relational database is one with that conforms to a relational data model or schema. Relations are captured in databases of this type by a number of tables comprising one or more rows and columns representing records and fields respectively. Data values are stored at the intersection of rows and columns. Each row represents a unique record of data from one or more columns, which enforce particular data types on values thereof.
Relationships between tables are expressed utilizing data values representing primary and foreign keys. A primary key is at least one column uniquely identifying a row in a table. A foreign key is one or more table columns whose values are the same as those of a primary key from another table. Relationships are expressed between tables by matching foreign keys in tables to primary keys of other tables.
A structured query language (SQL) is the standard computer language for relational database interaction. SQL is a set-based declarative language designed specifically for creating, retrieving, updating and deleting relational data. Standard commands such as “Select,” “Update,” “Delete,” “Create,” “Drop” and variations thereon can be utilized to accomplish most any database task. For example, to retrieve data from a table the following SQL syntax can be specified: “SELECT column_name(s) FROM table_name.” The result is a table of rows from the identified table including designated column names.
While data can be retrieved from a single table, many situations require data housed in multiple tables. To obtain data across tables a join needs to be employed to combine rows of various tables. There are a number of different types of joins supported by SQL. The most common join (often the default) is what is referred to as an inner join. An inner join returns all rows from multiple tables where a join condition is satisfied. In essence, an inner join finds the intersection between tables. It is noted that care needs to be taken when using an inner join because rows that include no values or the null value will not be returned, as they will not likely match a join condition.
Another type of join is outer join including further subtypes left and right. In general, an outer join returns all rows from one table (e.g., left, right) and only those rows from other tables that match a join condition. Accordingly, every row will be returned from one table and if there is no matching row in the other table, null will be specified for corresponding columns.